import { format } from '@/common/utils/date'
import { command } from '@/server/mysql'
import { humpToLine } from '@/common/utils/utils'
export function getFieldValue(val: any): string {
  let newVal = val
  if (val === 0) {
    newVal = 0
  } else if (val === true) {
    newVal = 1
  } else if (val === false) {
    newVal = 0
  } else if (!val) {
    newVal = 'NULL'
  }
  return newVal === 'NULL' ? 'NULL' : `'${newVal}'`
}
// 插入数据依赖于Object.keys方法返回的顺序稳定，如果遇到顺序混乱的问题，应该使用map
export function getFields(fieldObj: object): string {
  return Object.keys(fieldObj)
    .map((item) => humpToLine(item.trim()))
    .join()
}
export function getFieldValues(fieldObj: object): string {
  return Object.values(fieldObj)
    .map((val) => getFieldValue(val))
    .join()
}
export function addInsert(sql: string, fields = '', values = ''): string {
  const beforePartInfo: any = sql.match(/\)\s+VALUES\s+\(/i)
  if (!beforePartInfo.index) {
    return ''
  }
  const valuesStr = beforePartInfo[0]
  const fieldsPart = sql.slice(0, beforePartInfo.index)
  const valuesPart = sql.slice(beforePartInfo.index + valuesStr.length)
  const valuesPartBefore = valuesPart.slice(0, valuesPart.lastIndexOf(')'))
  const valuesPartAfter = valuesPart.slice(valuesPart.lastIndexOf(')'))
  // 未来可以扩展创建人ID，创建人name，修改人id，修改人name
  const commonInsertFieldsStr = ',created_at,updated_at'
  const newDate = format(new Date())
  const commonInsertValuesStr = `,'${newDate}', '${newDate}'`
  return `${fieldsPart} ${fields === null ? '' : fields || commonInsertFieldsStr} ${valuesStr} ${valuesPartBefore} ${
    values === null ? '' : values || commonInsertValuesStr
  } ${valuesPartAfter}`
}
export function addUpdate(sql: string): string {
  const beforePartInfo: any = sql.match(/\s+WHERE\s+/i)
  if (!beforePartInfo.index) {
    return ''
  }
  const beforePart = sql.slice(0, beforePartInfo.index)
  const afterPart = sql.slice(beforePartInfo.index)
  const newDate = format(new Date())
  const commonUpdateStr = `,updated_at='${newDate}'`
  return `${beforePart} ${commonUpdateStr} ${afterPart}`
}

export async function commandTotal({ tableName, where }: { tableName: string; where: string }) {
  return (await command(`SELECT COUNT(*) AS total from ${tableName} ${where};`)).results[0].total
}
export function getPaginationStr({ pageSize, offset }: { pageSize: number; offset: number }) {
  return `LIMIT ${pageSize} ${offset === 0 ? '' : `OFFSET ${offset}`}`
}
export function getOrderBy({
  orderField = '',
  orderDirection = '',
  config = {},
}: {
  orderField?: string
  orderDirection?: string // ASC DESC
  config?: any
}) {
  let orderFieldStr = 'created_at'
  let orderDirectionStr = 'DESC'
  if (config.isCustom) {
    orderFieldStr = ''
    orderDirectionStr = ''
  }
  const orderFieldStrList = (
    (config.defaultOrderFieldStr ? `${config.defaultOrderFieldStr},` : '') + orderFieldStr
  ).split(',')
  const orderDirectionStrList = (
    (config.defaultOrderDirectionStr ? `${config.defaultOrderDirectionStr},` : '') + orderDirectionStr
  ).split(',')
  const orderFieldList = orderField ? orderField.split(',') : []
  const orderDirectionList = orderField ? orderDirection.split(',') : []
  orderFieldStrList.forEach((item, index) => {
    if (!orderFieldList.includes(item)) {
      orderFieldList.push(item)
      orderDirectionList.push(orderDirectionStrList[index])
    }
  })
  if (!orderFieldList.length) {
    return ''
  }
  const orderList = orderFieldList.map((item, index) => {
    return {
      orderField: item,
      orderDirection: orderDirectionList[index],
    }
  })
  return `ORDER BY ${orderList.map((item) => `${humpToLine(item.orderField)} ${item.orderDirection}`).join(', ')}`
}
export function formatWhere(where: string) {
  if (!where.includes('AND')) {
    return where
  } else {
    return where.replace(/1=1\s+AND/i, '').replace(/1=1\s+OR/i, '')
  }
}

function valueIsNotEmpty(value: any) {
  if (value === 0) {
    return true
  }
  if (value) {
    if (typeof value === 'object') {
      // 数组
      return value.length > 0
    } else {
      return true
    }
  }
  return false
}

function getSearchWhereItem(item: any, config: any = {}) {
  let result = ''
  if (!valueIsNotEmpty(item.value)) {
    return result
  }
  const alias = item.alias || config.defAlias
  const field = `${alias}.${item.field}`
  result = `${item.join === 'OR' ? 'OR' : 'AND'} `
  switch (item.type) {
    case 'custom':
      result += item.where
      break
    case 'between':
      result += `${field} BETWEEN '${item.value[0]}' AND '${item.value[1]}'`
      break
    case 'like':
      result += `${field} like '${item.l === false ? '' : '%'}${item.value}${item.r === false ? '' : '%'}' `
      break
    case 'in':
      result += `${field} IN (${item.value.map((v) => `'${v}'`).join()}) `
      break
    case '=':
    case '>=':
    case '>':
    case '<':
    case '<=':
      result += `${field} ${item.type} '${item.value}' `
      break
    default:
      break
  }
  return result
}

/**
 * 创建where条件
 * @param schema
 * @param config
 */
export function createWhere(schema = [], config = {}) {
  let condition = ''
  for (let i = 0; i < schema.length; i++) {
    const item = schema[i]
    if (!item.type) {
      if (Array.isArray(item.value)) {
        item.type = 'in'
      }
    }
    item.type = item.type || '='
    condition += getSearchWhereItem(item, config)
  }
  if (condition) {
    if (condition.startsWith('OR ')) {
      condition = condition.replace('OR ', '')
    } else if (condition.startsWith('AND ')) {
      condition = condition.replace('AND ', '')
    }
    return `WHERE ${condition}`
  }
  return ''
}
// 传入null则表示要将该值设置为null，传入undefined则表示要忽略该值，不进行更新
export function getUpdateValue(val: string) {
  return val === null ? `NULL` : `'${val}'`
}
export function getUpdateContent(fieldObj: any) {
  return Object.keys(fieldObj)
    .filter((key) => fieldObj[key] !== undefined)
    .map((key) => `${humpToLine(key)}=${getUpdateValue(fieldObj[key])}`)
    .join()
}

export function getStatusCondition(status: any, startWithAnd = true, tableAlias = '') {
  return `${
    status === '2'
      ? ` ${startWithAnd ? 'AND' : ''} (${tableAlias ? `${tableAlias}.` : ''}status = '2' OR ${
          tableAlias ? `${tableAlias}.` : ''
        }status IS NULL)`
      : `${!status ? '' : ` ${startWithAnd ? 'AND' : ''} ${tableAlias ? `${tableAlias}.` : ''}status = '${status}'`}`
  }`
}
